package fun.sboy.api;

import com.mysql.cj.protocol.Resultset;
import fun.sboy.pojo.ManagerUser;
import fun.sboy.pojo.Trade;
import fun.sboy.pojo.TradeVO;
import fun.sboy.util.JDBCUtil;
import fun.sboy.view.HomeView;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.UUID;

public class API {

    private static Random random = new Random();
    private static final int NUM = 999;

    /**
     * 用户登录
     *
     * @param username
     * @param password
     * @return
     */
    public static Boolean login(String username, String password) {
        String sql = "SELECT * FROM manager_user WHERE binary username = ? AND binary password = ?";
        ManagerUser managerUser = JDBCUtil.selectOne(sql, ManagerUser.class, username, password);
        if (managerUser == null) {
            return false;
        } else {
            HomeView.user = managerUser;
            return true;
        }
    }


    /**
     * 注册
     *
     * @return
     */
    public static Integer register(String[] registerInfo) {
        String sql = "INSERT INTO manager_user VALUES(NULL,?,?,?,?,100,?,?,?)";
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < registerInfo.length; i++) {
                String val = registerInfo[i];
                preparedStatement.setObject(i + 1, val);
            }

            preparedStatement.setObject(7, "sunboy" + registerInfo[2] + random.nextInt(NUM));

            int column = preparedStatement.executeUpdate();
            return column;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeSource(connection, preparedStatement, null);
        }

        return null;
    }


    /**
     * 判断用户是否存在
     *
     * @param username
     * @return
     */
    public static Boolean usernameExists(String username) {
        String sql = "SELECT * FROM manager_user WHERE username = ?";
        ManagerUser user = JDBCUtil.selectOne(sql, ManagerUser.class, username);
        if (user == null) {
            return false;
        } else {
            return true;
        }
    }


    /**
     * 获得当前用户的交易订单
     *
     * @return
     */
    public static List<TradeVO> listTrade() {

        ArrayList<TradeVO> resultList = new ArrayList<>();
        ManagerUser user = HomeView.user;
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement psst = null;
        ResultSet resultSet = null;
        try {
            String sql = "SELECT * FROM trade WHERE remitter = ? OR payee = ? ORDER BY create_time DESC LIMIT 0,25";
            psst = connection.prepareStatement(sql);
            psst.setInt(1, user.getId());
            psst.setInt(2, user.getId());
            resultSet = psst.executeQuery();

            Class<Trade> tclass = Trade.class;
            Field[] fields = tclass.getDeclaredFields();
            ArrayList<Trade> list = new ArrayList<>();
            while (resultSet.next()) {
                Trade trade = tclass.newInstance();
                for (Field field : fields) {
                    field.setAccessible(true);
                    String name = JDBCUtil.fieldNameToSqlDataName(field.getName());
                    Object val = resultSet.getObject(name);
                    field.set(trade, val);
                }
                list.add(trade);
            }
            JDBCUtil.closeSource(null, psst, resultSet);

            for (Trade trade : list) {
                TradeVO tradeVO = new TradeVO();
                sql = "SELECT * FROM manager_user WHERE id = ?";
                psst = connection.prepareStatement(sql);
                if (trade.getPayee() == user.getId()) {
                    psst.setInt(1, trade.getRemitter());
                    tradeVO.setPayeeOrRemitter("收入");
                } else {
                    psst.setInt(1, trade.getPayee());
                    tradeVO.setPayeeOrRemitter("支出");
                }
                resultSet = psst.executeQuery();
                resultSet.next();
                tradeVO.setMoney(trade.getMoney());
                tradeVO.setCreateTime(trade.getCreateTime());
                tradeVO.setName(resultSet.getString("name"));
                tradeVO.setCardNumber(resultSet.getString("card_number"));
                resultList.add(tradeVO);
            }
            return resultList;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeSource(connection, psst, resultSet);
        }
        return null;
    }


    /**
     * 判断转账用户的名字和卡号是否正确
     *
     * @param name
     * @param cardNumber
     * @return
     */
    public static ManagerUser isNameAndCardNumber(String name, String cardNumber) {
        String sql = "SELECT * FROM manager_user WHERE name =  ? AND card_number = ?";
        ManagerUser user = JDBCUtil.selectOne(sql, ManagerUser.class, name, cardNumber);
        return user;
    }


    /**
     * 获得剩余金额
     *
     * @return
     */
    public static Double getMoneyBalance() {
        String sql = "SELECT money FROM manager_user WHERE username = ?";
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,HomeView.user.getUsername());
            resultSet = preparedStatement.executeQuery();
            resultSet.next();
            return resultSet.getDouble("money");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeSource(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 提交转账订单
     *
     * @param currentUserBalanceMoney
     * @param remitterMoney
     * @param payeeUser
     */
    public static void submitOrder(Double currentUserBalanceMoney, Double remitterMoney, ManagerUser payeeUser) throws SQLException {
        ManagerUser user = HomeView.user;
        Connection connection = null;
        PreparedStatement psst = null;
        connection = JDBCUtil.getConnection();
        connection.setAutoCommit(false);

        //更新用户的剩余金额
        String sql = "UPDATE manager_user set money = ? WHERE username = ?";
        psst = connection.prepareStatement(sql);

        //1修改当前用户的余额
        psst.setDouble(1, currentUserBalanceMoney - remitterMoney);
        psst.setString(2, user.getUsername());
        psst.executeUpdate();

        //2修改目标用户的余额    TODO 存在安全问题，处于单机状态安全问题不复存在
        psst.setDouble(1, payeeUser.getMoney() + remitterMoney);
        psst.setString(2, payeeUser.getUsername());
        psst.executeUpdate();

        //3结束资源
        psst.close();


        //4添加转账记录
        sql = "INSERT INTO trade VALUES(NULL,?,?,?,CURRENT_TIMESTAMP)";
        psst = connection.prepareStatement(sql);
        psst.setInt(1, user.getId());
        psst.setInt(2, payeeUser.getId());
        psst.setDouble(3, remitterMoney);
        psst.execute();
        psst.close();

        //5提交订单
        connection.commit();
        JDBCUtil.closeSource(connection, psst, null);
    }
}
